﻿/*
Copyright (C) 2009  Tomasz Chrzanowski

This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program.  If not, see <http://www.gnu.org/licenses/>
*/

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace Com.TomCdc.ChangeDataCapture
{
    class AdvancedSourceRecordIdProvider
    {
        /// <summary>
        /// Represents a sql connection string to the database with the selector procedures.
        /// </summary>
        private readonly string _selectorDbConnString;

        /// <summary>
        /// Represents the dictionary of the selectors and associated sql command objects prepared for the execution.
        /// If the <see cref="SqlCommand"/> is null, the selector was not activated yet. Activation takes place in the
        /// <see cref="DeriveProcedureParameters"/> method.
        /// </summary>
        private readonly Dictionary<string, SqlCommand> _selectorProcedures;

        /// <summary>
        /// Represents the sql query to select the selector procedures.
        /// </summary>
        private const string SQL_SELECT_SELECTORS = @"
            SELECT
                Name SelectorName
            FROM
                sysobjects
            WHERE
                TYPE = 'P' AND 
                NAME LIKE '" + CommonHelper.SELECTOR_SP_PREFIX + "%'";

        /// <summary>
        /// Initializes a new instance of <see cref="AdvancedSourceRecordIdProvider"/> class with the connection
        /// string to the selector database.
        /// </summary>
        /// <param name="monitoredConnString">The string that is valid sql connection string.</param>
        public AdvancedSourceRecordIdProvider(string selectorDbConnString)
        {
            // all inputs validation
            if (string.IsNullOrEmpty(selectorDbConnString))
            {
                throw new ArgumentException("The monitoredConnString argument is null or empty.");
            }

            _selectorDbConnString = selectorDbConnString;

            _selectorProcedures = new Dictionary<string, SqlCommand>();
        }

        /// <summary>
        /// Gets the list of selector sql procedures. All procedures are wrapped  in <see cref="SelectorProcedure"/> 
        /// class.
        /// </summary>
        /// <returns>The list of selector sql procedures.</returns>
        public SelectorProcedure[] GetSelectorsList()
        {
            _selectorProcedures.Clear();
            using (SqlConnection sqlConn = new SqlConnection(_selectorDbConnString))
            using (SqlCommand sqlCmd = new SqlCommand(SQL_SELECT_SELECTORS, sqlConn))
            {
                sqlConn.Open();

                using (SqlDataReader sqlReader = sqlCmd.ExecuteReader())
                {
                    int colSelectorNameIdx = sqlReader.GetOrdinal("SelectorName");

                    while (sqlReader.Read())
                    {
                        // populates collection with selector procedure names
                        _selectorProcedures.Add(sqlReader.GetString(colSelectorNameIdx), null);
                    }
                }
            }

            // prepares array of the SelectorProcedure objects
            SelectorProcedure[] result = new SelectorProcedure[_selectorProcedures.Count];
            int i = 0;
            foreach (string selName in _selectorProcedures.Keys)
            {
                result[i++] = new SelectorProcedure(selName);
            }

            return result;
        }

        /// <summary>
        /// Retrieves parameter list of provided strored procedure and assing ready to execute 
        /// <see cref="SqlCommand"/> object to <see cref="_selectorProcedures"/> collection.
        /// </summary>
        /// <param name="procedureName">The name of a stored procedure.</param>
        private void DeriveProcedureParameters(string procedureName)
        {
            // validate input prameter
            if (!_selectorProcedures.ContainsKey(procedureName))
            {
                throw new ArgumentException("The procedure name provided in procedureName argument does not exisit ");
            }

            // if parameters already derived for given procedure end execution
            if (_selectorProcedures[procedureName] != null)
            {
                return;
            }

            using (SqlConnection sqlConn = new SqlConnection(_selectorDbConnString))
            using (SqlCommand sqlCmd = new SqlCommand(procedureName, sqlConn))
            {
                sqlConn.Open();
                sqlCmd.CommandType = CommandType.StoredProcedure;
                SqlCommandBuilder.DeriveParameters(sqlCmd);
                // removes reference to sqlConn object, so that it can be disposed when goes out of scope
                sqlCmd.Connection = null;
                _selectorProcedures[procedureName] = sqlCmd;
            }
        }

        /// <summary>
        /// Populates <see cref="DataGridView"/> with list of selector procedure parameters.
        /// </summary>
        /// <param name="selectorParamsGrid">The <see cref="DataGridView"/> object which will be populated
        /// with procedure parameters.</param>
        /// <param name="selectedItem">The <see cref="SelectorProcedure"/> as <see cref="object"/>.</param>
        public void PopulateGridParametersList(DataGridView selectorParamsGrid, object selectedItem)
        {
            SelectorProcedure selectedProc = selectedItem as SelectorProcedure;
            if (selectedProc == null)
            {
                throw new ArgumentException("The selectedItem argument is not of the SelectorProcedure type.");
            }

            DeriveProcedureParameters(selectedProc.ProcedureName);

            selectorParamsGrid.Rows.Clear();
            foreach(SqlParameter sqlParm in _selectorProcedures[selectedProc.ProcedureName].Parameters)
            {
                if (sqlParm.Direction == ParameterDirection.Input)
                {
                    int newRowIdx = selectorParamsGrid.Rows.Add();
                    selectorParamsGrid["Argument", newRowIdx].Value = new SelectorProcedureParam(sqlParm.ParameterName);
                }
            }
            selectorParamsGrid.Columns["Value"].ReadOnly = false;
        }


        /// <summary>
        /// Gets the list of source record IDs for given stored procedure given in <see cref="SelectorProcedure"/> 
        /// (wrapped in <see cref="object"/>) and list of parameters provided in <see cref="DataGridView"/>. 
        /// </summary>
        /// <param name="selectorParamsGrid">The <see cref="DataGridView"/> object with list of procedure 
        /// parameters.</param>
        /// <param name="selectedProcedure">The selector stored procedure name.</param>
        /// <returns>The list of source record IDs.</returns>
        public ISelector<SourceRecord> GetSourceRecordsId(DataGridView selectorParamsGrid, object selectedProcedure)
        {
            // input arguments validations
            SelectorProcedure selectedProc = selectedProcedure as SelectorProcedure;
            if (selectedProc == null)
            {
                throw new ArgumentException("The selectedItem argument is not of the SelectorProcedure type.");
            }

            if (!_selectorProcedures.ContainsKey(selectedProc.ProcedureName))
            {
                throw new ArgumentException("The procedure name does not exist in the dictionary.");
            }

            SqlCommand sqlCmd = _selectorProcedures[selectedProc.ProcedureName];

            if (sqlCmd == null)
            {
                throw new CdcViewerException("The selected procedure has not been initialized and cannot be used.");
            }

            // collect parameters from the grid object and assign its values to sql command object parameters
            foreach (DataGridViewRow gridRow in selectorParamsGrid.Rows)
            {
                SelectorProcedureParam procParamName = gridRow.Cells["Argument"].Value as SelectorProcedureParam;
                if (procParamName == null)
                {
                    throw new ArgumentException("The selectorParamsGrid argument " +
                        "does not contain proper data in the rows.");
                }
                sqlCmd.Parameters[procParamName.ParamName].Value = gridRow.Cells["Value"].Value;
            }

            SourceRecord[] returnedIds;

            using (SqlConnection sqlConn = new SqlConnection(_selectorDbConnString))
            {
                sqlCmd.Connection = sqlConn;
                // changes param direction to Output only, when param list derived by SqlCommandBuilder.DeriveParameters
                // method this param gets direction InputOutput and requires a value assigned
                sqlCmd.Parameters["@TableName"].Direction = ParameterDirection.Output;
                sqlConn.Open();

                List<Int64> ids = new List<Int64>();

                try
                {
                    using (SqlDataReader sqlReader = sqlCmd.ExecuteReader())
                    {
                        while (sqlReader.Read())
                        {
                            ids.Add(Convert.ToInt64(sqlReader[0]));
                        }
                    }
                }
                catch (FormatException)
                {
                    // when user provided value of parameter of incorrect type the exception is thrown
                    throw new CdcViewerException("The value of one or more parameters is in incorrect format.");
                }

                // output parameter cannot be accessed before closing the sql reader
                string sourceTable = (string)sqlCmd.Parameters["@TableName"].Value;

                // wraps the IDs in SourceRecord object
                returnedIds = new SourceRecord[ids.Count];
                for (int i = 0; i < returnedIds.Length; i++)
                {
                    returnedIds[i] = new SourceRecord(sourceTable, ids[i]);
                }

                sqlCmd.Connection = null;
            }

            ISelector<SourceRecord> result = new Selector<SourceRecord>(returnedIds);
            return result;
        }


    }

    /// <summary>
    /// The class used for wrapping procedure parameter name.
    /// </summary>
    class SelectorProcedureParam
    {
        private string _paramName;
        private string _paramNameFormated;

        public string ParamName
        {
            get { return _paramName; }
        }

        public SelectorProcedureParam(string procParamName)
        {
            _paramName = procParamName;

            // cuts off parameter first character (@) and formats it
            _paramNameFormated = CommonHelper.FormatPascalString(procParamName.Substring(1));
        }

        public override string ToString()
        {
            return _paramNameFormated;
        }
    }

    /// <summary>
    /// The class used for wrapping sql procedure name.
    /// </summary>
    class SelectorProcedure
    {
        private string _procName;
        private string _procNameFormated;

        public string ProcedureName
        {
            get { return _procName; }
        }

        public SelectorProcedure(string procedureName)
        {
            _procName = procedureName;
            
            // cuts off procedure prefix and formats it
            _procNameFormated = CommonHelper.FormatPascalString(
                procedureName.Substring(CommonHelper.SELECTOR_SP_PREFIX.Length));
        }

        public override string ToString()
        {
            return _procNameFormated;
        }

    }
}
